USE [comunion]
GO

/****** Object:  View [dbo].[V_SuppliesBalanceInquiry_Module]    Script Date: 05/12/2010 15:58:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER  VIEW [dbo].[V_SuppliesBalanceInquiry_Module] AS

SELECT 
A.cCompanyID, A.cSupplyNo, 
ISNULL(SUM(A.nInitial), 0) AS nInitial, 
ISNULL(SUM(A.nIncoming), 0) AS nIncoming, 
ISNULL(SUM(A.nOutgoing), 0) AS nOutgoing, 
ISNULL(SUM(A.nAdjustment), 0) AS nAdjustment,
ISNULL((sum(A.nInitial) - sum(A.nOutgoing) + sum(A.nIncoming) + sum(A.nAdjustment)), 0) AS nBalance 
FROM
(
(
--Initial
SELECT A.cCompanyID, A.cSupplyNo, 
SUM(A.nInitial) AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nAdjustment
FROM SUPPLY A 
GROUP BY A.cCompanyID, A.cSupplyNo
)
UNION ALL
(
--Incoming
SELECT A.cCompanyID, A.cItemNo as cSupplyNo, 
0 AS nInitial, SUM(A.nQty) AS nIncoming, 0 AS nOutgoing, 0 AS nAdjustment
FROM WRR_T A 
LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType = 'Purchase' AND B.lCancelled = 0 AND B.lClosed = 0 
GROUP BY A.cCompanyID, A.cItemNo
)
UNION ALL
(
--Outgoing
SELECT A.cCompanyID, A.cItemNo as cSupplyNo,
0 AS nInitial, 0 AS nIncoming, SUM(A.nQty) AS nOutgoing, 0 AS nAdjustment
FROM SUPPLYISSUANCE_T A 
LEFT OUTER JOIN SUPPLYISSUANCE B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE  B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo
)
UNION ALL
(
--Borrowed
SELECT A.cCompanyID, A.cItemNo as cSupplyNo,
0 AS nInitial, 0 AS nIncoming, SUM(A.nQty) AS nOutgoing, 0 AS nAdjustment
FROM SBS_T A 
LEFT OUTER JOIN SBS B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
--WHERE  B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo
)
--UNION ALL
--(
----Returned
--SELECT A.cCompanyID, A.cItemNo as cSupplyNo,
--0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, SUM(A.nQty) AS nAdjustment
--FROM SRS_T A 
--LEFT OUTER JOIN SRS B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
----WHERE  B.lCancelled = 0 AND B.lClosed = 0
--GROUP BY A.cCompanyID, A.cItemNo
--)
UNION ALL
(
--Inventory Adjustment
SELECT A.cCompanyID, A.cItemNo as cSupplyNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, SUM(nCount - nBalance) AS nAdjustment
FROM INVADJ_T A 
LEFT OUTER JOIN INVADJ B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo
)) A
LEFT OUTER JOIN SUPPLY B ON A.cSupplyNo = B.cSupplyNo AND A.cCompanyID = B.cCompanyID
GROUP BY A.cCompanyID, A.cSupplyNo









GO


